How can I create and use a calculated column in a SQL Server query?

Question

Ravi Vishwakarma · Answer

Creating and using calculated columns in an 
SQL Server query allows you to generate values based on existing data in your table. These calculated columns can be used in
SELECT statements or defined directly in the table schema.and 
Here's how to create and use calculated columns in both ways:
1. Using Calculated Columns in a SELECT Statement
You can create calculated columns directly in your SELECT statement by using expressions or functions.
CREATE TABLE CollageStudents (
 StudentID INT PRIMARY KEY, -- Create StudentID with primary key
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 DOB DATE,
 [Address] VARCHAR(100),
 Age AS DATEDIFF(YEAR, DOB, GETDATE()) -- Calculated column for Age
);
INSERT INTO CollageStudents (StudentID, FirstName, LastName, DOB, [Address])
VALUES
(1, 'John', 'Doe', '2000-05-15', '123 Main St, City'),
(2, 'Jane', 'Smith', '2001-10-20', '456 Oak Ave, Town'),
(3, 'Michael', 'Johnson', '1999-03-08', '789 Elm Rd, Village');
Querying the Table
Now, let's query the CollageStudents table to see how the calculated column
Age works:
SELECT StudentID, FirstName, LastName, DOB, Age, Address
FROM CollageStudents;
and 
Explanation
Table Definition:
CollageStudents table has columns StudentID, 
FirstName, LastName, DOB, Address.Age is a calculated column using the AS keyword and the
DATEDIFF function to calculate the difference in years between 
DOB and the current date (GETDATE()).
Querying the Table:
The SELECT statement retrieves all columns including Age, which is computed dynamically based on the
DOB of each student.
Additional Notes
Updating Calculated Columns: Calculated columns are automatically updated whenever a row is inserted or updated. They provide a convenient way to derive data based on existing columns.Performance Considerations: While calculated columns simplify querying by dynamically computing values, they should be used judiciously in scenarios where performance implications are minimal.
and 
Modify the Table and Create a new Computed Column
ALTER TABLE CollageStudents
ALTER COLUMN DOB DATETIME -- Change the column datatype
ADD FullName AS (FirstName + ' ' + LastName); -- Add New Computed or Calculated column
Let's see in table
Querying the Table
Now, let's query the CollageStudents table to see how the calculated column
Age works:
SELECT * FROM CollageStudents;
and 
and 
You can create a computed column with conditions.
Create ParmanantAddress when IsSameAddressand value is true.
ALTER TABLE CollageStudents
ADD IsSameAddress BIT NOT NULL DEFAULT 0
ADD ParmanantAddress AS (
 CASE 
 WHEN IsSameAddress = 1 THEN [Address] 
 ELSE NULL 
 END
);
-- Update some values 
update CollageStudents
set IsSameAddress = 1
where StudentID = 3
-- Run it
SELECT * FROM CollageStudents;
Output
and 
Read moreand 
Explain the SQL Server backups and their types
Explain the SQL triggers and their uses
Define the PIVOT Table with examples in SQL server.
Differences between stored procedures and functions in SQL

forum

How can I create and use a calculated column in a SQL Server query?

Ravi Vishwakarma

1 Answers

1. Using Calculated Columns in a `SELECT` Statement

Querying the Table

Explanation

Additional Notes

Modify the Table and Create a new Computed Column

Querying the Table

You can create a computed column with conditions.

Liked By